const connection = require('../app/database')

class ScoresService {
  async getScoresList(pagenum = '1', pagesize= '10', sportName='', athleteName='',collegeId='',sportId='',athleteId='') {
    const statement = `
      SELECT (SELECT COUNT(*) FROM athletes_sports) totalCount,
      JSON_ARRAYAGG(JSON_OBJECT('id',t.id,'sportName',t.sport_name,'athleteName',t.athlete_name,'time',t.sport_time,'athleteId',t.athlete_id,'sportId',t.sport_id,'score',t.score,'rank',t.ranking,'collegeName',t.college_name)) list
      FROM (
      SELECT s.sport_name,a.athlete_name,s.sport_time,s.sport_id,a.athlete_id,aths.id id,aths.score,aths.ranking,c.college_name
      FROM athletes_sports aths JOIN sports s 
      ON aths.sport_id = s.sport_id JOIN athletes a 
      ON a.athlete_id = aths.athlete_id JOIN colleges c
      ON a.college_id = c.college_id
      WHERE s.sport_id LIKE '%${sportId}%' 
      AND a.athlete_id LIKE '%${athleteId}%' 
      AND s.sport_name LIKE '%${sportName}%' 
      AND a.athlete_name LIKE '%${athleteName}%'
      AND c.college_id LIKE '%${collegeId}%'
      LIMIT ?,?) t;
    `
    const offset = (pagenum - 1) * pagesize
    const [res] = await connection.execute(statement,[offset+'',pagesize])
    return res[0]
  }
  async updateScore(score, rank,id) {
    const statement = `
      UPDATE athletes_sports SET score = ?,ranking = ?
      WHERE id = ?;
    `
    const [res] = await connection.execute(statement,[score, rank,id])
    return res
  }
}

module.exports = new ScoresService()